import copy
import json
import multiprocessing
from .common_func.print_logger import PrintLogger as Logger
import_error_warned = 0
try:
    import peewee
    from peewee import OperationalError, InterfaceError, DatabaseError

    from playhouse.pool import PooledPostgresqlDatabase, PooledSqliteDatabase, PooledMySQLDatabase
    from playhouse.shortcuts import ReconnectMixin
except ModuleNotFoundError:
    if import_error_warned < 4:
        Logger.warning("db model is not valid, because require module:[peewee] is not installed. "
                       "If you want use u_db_model, please install [peewee] first")
        import_error_warned += 1
try:
    import psycopg2
except ModuleNotFoundError:
    if import_error_warned < 4:
        Logger.warning("Postgres is not valid for peewee, because require module:[psycopg2-binary] is not installed. "
                       "If you want use Postgres, please install [psycopg2-binary] first")
        import_error_warned += 1

try:
    import sqlite3
except ModuleNotFoundError:
    if import_error_warned < 4:
        Logger.warning("Sqlite is not valid for peewee, because require module:[sqlite3] is not installed. "
                       "If you want use sqlite, please install [sqlite3] first")
        import_error_warned += 1

try:
    import pymysql
except ModuleNotFoundError:
    if import_error_warned < 4:
        Logger.warning("Mysql and MariaDB is not valid for peewee, because require module:[pymysql] is not installed. "
                       "If you want use Postgres, please install [pymysql] first")
        import_error_warned += 1


from .u_db_dm8 import PooledDM8Database


class BasicModel(peewee.Model):
    def __str__(self):
        """
        格式化成json字符串
        :return:
        """
        result = dict()
        for key, value in self.__data__.items():
            try:
                json.dumps(value)
                result[key] = value
            except TypeError:
                result[key] = str(value)
        return json.dumps(result)

    class Meta:
        database = None

    def get_json_data(self):
        """
        获取模型数据，主要用于查询结果的展示和使用。
        :return: 数据字典，{字段名1：字段值,字段名2：字段值,...}
        """
        r = copy.deepcopy(self.__dict__)
        r.pop("_dirty")
        r.pop('__rel__')
        r.pop('__data__')
        r.update(self.__data__)

        return r

    @staticmethod
    def build_join_arg(model, on, join_type="INNER JOIN"):
        return {"model": model, "on": on, "join_type": join_type}

    @classmethod
    def field_name_2_object(cls, field_name: str):
        """
        通过字段名返回Model中的字段对象
        :param field_name:
        :param cls:
        :return:
        """
        return cls._meta.__dict__.get("fields").get(field_name)

    @classmethod
    def get_table_name(cls):
        """
        获取表名
        :param cls:
        :return:
        """
        return cls._meta.table_name

    @classmethod
    def get_field_name_list(cls):
        """
        获取字段名列表
        :param cls:
        :return:
        """
        return list(cls._meta.fields.keys())

    @staticmethod
    def _arg2list(arg):
        """
        将除列表或元组外的其他参数类型转换为列表
        :param arg: 参数
        :return:
        """
        if arg is None:
            return list()
        if isinstance(arg, tuple) or isinstance(arg, list):
            return arg
        return [arg]

    @classmethod
    def complex_select(cls, **kwargs):
        """
        复合查询

        :param cls:
        :param kwargs:
        :return:
        """
        fields_arg = cls._arg2list(kwargs.get("fields", list()))
        where_arg = cls._arg2list(kwargs.get("where", list()))
        join_arg = cls._arg2list(kwargs.get("join", list()))
        order_by = kwargs.get("order_by", None)
        limit = kwargs.get("limit", None)
        result_type = kwargs.get("result_type", "property")
        query = cls.select(*fields_arg)
        for join_cond in join_arg:
            join_model = join_cond.get("model")
            on_condition = join_cond.get("on")
            join_type = join_cond.get("join_type")
            query = query.join(join_model, join_type=join_type, on=on_condition)
        if where_arg:
            query = query.where(*where_arg)
        if order_by:
            query = query.order_by(order_by)
        if limit:
            query = query.limit(limit)

        if result_type == "dict":
            return [cls.get_json_data(row) for row in query.objects()]
        else:
            return [row for row in query.objects()]

    @classmethod
    def unique_select(cls, **kwargs):
        """
        查询唯一值
        kwarg中支持的参数包括 fields where join，这几个用法与complex_select一致，由于只返回一个值，所以去掉了排序和返回值数量限制
        增加一个默认值参数：default_result，在查不到结果或出错时，返回默认值，不设置则返回None
        如果查询出多个结果，返回第一个的值
        如果查询多字段，则返回字段值列表，单字段则返回该字段值，无字段参数，则返回第一行数据
        :param cls:
        :param kwargs:
        :return:
        """
        default_result = kwargs.get("default_result", None)
        # noinspection PyBroadException
        try:
            fields_arg = cls._arg2list(kwargs.get("fields", list()))
            where_arg = cls._arg2list(kwargs.get("where", list()))
            join_arg = cls._arg2list(kwargs.get("join", list()))
            result_type = kwargs.get("result_type", "property")

            query = cls.select(*fields_arg)
            for join_cond in join_arg:
                join_model = join_cond.get("model")
                on_condition = join_cond.get("on")
                join_type = join_cond.get("join_type")
                query = query.join(join_model, join_type=join_type, on=on_condition)

            if where_arg:
                query = query.where(*where_arg)
            results = [row for row in query.objects()]
            if not results:
                return default_result
            result = results[0]
            if result_type == "dict":
                return cls.get_json_data(result)
            elif result_type == "list":
                res = list(cls.get_json_data(result).values())
                if len(res) == 1:
                    return res[0]
                else:
                    return res
            else:
                return result
        except Exception:
            return default_result

    @classmethod
    def get_field_value_set(cls, field_name: str):
        """
        获取数据库表的所有field值，返回集合
        :param field_name: 字段名
        :return:
        """
        results = cls.select(cls.field_name_2_object(field_name)).execute()

        res_set = set()
        if not results:
            return res_set
        for res in results:
            res_set.add(res.__data__[field_name])

        return res_set

    @classmethod
    def w_insert_many(cls, rows, fields=None):
        """
        insert_many如果一次传入行过多会有性能问题，因为函数内50行做一个批次进行操作
        1、当 rows 传递的是字典列表时，fields 是不需要传的，如果传了，那么，rows 中的字段在字典中必须存在，否则报错。
            如果没有传递 fields 参数，那么默认取所有字典的交集作为插入字段。
            这个也好理解，比如一个字典的键是a、b、c，一个是 b、c、d，那么就取 b、c 作为需要插入的字段。peewee 不会为缺失的字段做默认处理。
        2、当 rows 传递的是元组列表时，必须指定 fields，并且 fields 中字段名的顺序跟元组一致。
            元组中值的数量必须大于等于 fields 中字段的数量，一般建议是保持一致。
        :param rows:
        :param fields:
        :return:
        """
        batch = 0
        while batch * 50 < len(rows):
            op_rows = rows[batch * 50: batch * 50 + 50]
            cls.insert_many(op_rows, fields).execute()

    @classmethod
    def w_insert(cls, **kwargs):
        """
        保存数据内容
        :param cls:
        :param kwargs:
        :return:
        """
        if not kwargs:
            return

        # logger.debug(**kwargs.sql())
        return cls.create(**kwargs)

    @classmethod
    def w_replace(cls, __data=None, **insert):
        return cls.insert(__data, **insert).on_conflict('REPLACE').execute()

    @classmethod
    def w_count(cls, **kwargs):
        """
        某个查找结果的数量
        :param cls:
        :param kwargs:
        :return:
        """
        fields_arg = cls._arg2list(kwargs.get("fields", list()))
        where_arg = cls._arg2list(kwargs.get("where", list()))
        join_arg = cls._arg2list(kwargs.get("join", list()))
        order_by = kwargs.get("order_by", None)
        limit = kwargs.get("limit", None)
        query = cls.select(*fields_arg)
        for join_cond in join_arg:
            join_model = join_cond.get("model")
            on_condition = join_cond.get("on")
            join_type = join_cond.get("join_type")
            query = query.join(join_model, join_type=join_type, on=on_condition)
        if where_arg:
            query = query.where(*where_arg)
        if order_by:
            query = query.order_by(order_by)
        if limit:
            query = query.limit(limit)

        return query.count()

    @classmethod
    def w_update(cls, **kwargs):
        """
        执行UPDATE操作
        :param cls:
        :param kwargs:
        :return:
        """
        if "where" in kwargs:
            where_arg = cls._arg2list(kwargs.pop("where"))
            query = cls.update(**kwargs).where(*where_arg)
        else:
            query = cls.update(**kwargs)
        return query.execute()

    @classmethod
    def w_delete(cls, **kwargs):
        """
        删除
        :return:
        """
        where_arg = cls._arg2list(kwargs.get("where", None))
        if where_arg:
            return cls.delete().where(*where_arg).execute()
        else:
            return cls.delete().execute()

    @classmethod
    def execute_sql(cls, sql: str):
        """
        执行原生sql
        :param sql:
        :return:
        """
        db = cls.Meta.database
        if db is None:
            return list()
        return [row for row in db.execute_sql(sql)]


class ReconnectPooledPostgresqlDatabase(ReconnectMixin, PooledPostgresqlDatabase):
    reconnect_errors = (
        # Postgres error examples:
        (OperationalError, 'terminat'),
        (InterfaceError, 'connection already closed'),
    )


class ReconnectPooledMySQLDatabase(ReconnectMixin, PooledMySQLDatabase):
    pass


class ReconnectPooledDM8Database(ReconnectMixin, PooledDM8Database):
    # TODO: 确认下dm里的报错类型及关键字
    reconnect_errors = (
        # (OperationalError, 'terminat'),
        # (InterfaceError, 'connection already closed'),
        (DatabaseError, '-70019'),
        (DatabaseError, '-70028'),
        (DatabaseError, 'InValid handle')
    )


class DM8Manager(object):
    """
        数据库管理器基类
        通过继承使用，继承时给定参数单例
            db_conf: database config, dict type, format: {"host":xxx, "port": xxx, "user": xxx,
                    "password":xxx, "max_conn":xxx, "ping_interval":xxx}
        """
    _instance_lock = multiprocessing.Lock()
    __database = None
    _db_conf = dict()

    @classmethod
    def get_database(cls):
        """
        单例
        :return: 数据库连接池实例
        """
        if cls.__database is None:
            with cls._instance_lock:
                cls.__database = ReconnectPooledDM8Database(
                    host=cls._db_conf.get("host", '127.0.0.1'),
                    port=int(cls._db_conf.get("port", 5432)),
                    user=cls._db_conf.get("user", 'SYSDBA'),
                    database=cls._db_conf.get("dbname", 'dameng'),
                    password=cls._db_conf.get("password"),
                    max_connections=int(cls._db_conf.get("max_conn", 50)),
                    stale_timeout=int(cls._db_conf.get("ping_interval", 600)))
        return cls.__database


class PostgresManager(object):
    """
    数据库管理器基类
    通过继承使用，继承时给定参数单例
        db_conf: database config, dict type, format: {"dbname":xxx, "host":xxx, "port": xxx, "user": xxx,
                "password":xxx, "max_conn":xxx, "ping_interval":xxx}
    """
    _instance_lock = multiprocessing.Lock()
    __database = None
    _db_conf = dict()

    @classmethod
    def get_database(cls):
        """
        单例
        :return: 数据库连接池实例
        """
        if cls.__database is None:
            with cls._instance_lock:
                cls.__database = ReconnectPooledPostgresqlDatabase(
                    database=cls._db_conf.get("dbname"),
                    host=cls._db_conf.get("host", '127.0.0.1'),
                    port=int(cls._db_conf.get("port", 5432)),
                    user=cls._db_conf.get("user", 'postgres'),
                    password=cls._db_conf.get("password"),
                    max_connections=int(cls._db_conf.get("max_conn", 50)),
                    stale_timeout=int(cls._db_conf.get("ping_interval", 600)))
        return cls.__database


class MysqlManager(object):
    """
    数据库管理器基类
    通过继承使用，继承时给定参数单例
        db_conf: database config, dict type, format: {"dbname":xxx, "host":xxx, "port": xxx, "user": xxx,
                "password":xxx, "max_conn":xxx, "ping_interval":xxx}
    """
    _instance_lock = multiprocessing.Lock()
    __database = None
    _db_conf = dict()

    @classmethod
    def get_database(cls):
        """
        单例
        :return: 数据库连接池实例
        """
        if cls.__database is None:
            with cls._instance_lock:
                cls.__database = ReconnectPooledMySQLDatabase(
                    database=cls._db_conf.get("dbname"),
                    host=cls._db_conf.get("host", '127.0.0.1'),
                    port=int(cls._db_conf.get("port", 5432)),
                    user=cls._db_conf.get("user", 'postgres'),
                    password=cls._db_conf.get("password"),
                    max_connections=int(cls._db_conf.get("max_conn", 50)),
                    stale_timeout=int(cls._db_conf.get("ping_interval", 600)))
        return cls.__database


class SqLiteManager(object):
    """
    数据库管理器基类
    通过继承使用，继承时给定参数单例
        db_conf: database config, dict type, format: {"db_path":xxx, "max_conn":xxx}
    """
    _instance_lock = multiprocessing.Lock()
    __database = None
    _db_conf = dict()

    @classmethod
    def get_database(cls):
        """
        单例
        :return: 数据库连接池实例
        """
        if cls.__database is None:
            with cls._instance_lock:
                cls.__database = PooledSqliteDatabase(database=cls._db_conf.get("db_path"),
                                                      max_connections=int(cls._db_conf.get("max_conn", 50)))
        return cls.__database
